<?php
/*
 * emarket - a website to create your own online-shop.  
 * Copyright (C) 2009  Frédéric Delorme
 *
 * This file is part of emarket.
 *
 * emarket is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * emarket is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with emarket.  If not, see <http://www.gnu.org/licenses/>.
 *
 * To contact me: frederic.delorme@gmail.com
 * to see some information on te project : http://code.google.col/p/os-emarket/
 *
 */
global $framework;
require_once($framework."/persistence/datapersistence.php");

/**
 * Class to manage data into a MySQL database.
 * @author Frédéric Delorme<frederic.delorme@gmail.com>
 * @version 1.0
 */
class DataMySQL  extends DataPersistence{
	/**
	 * Instance foir the data manager.
	 * @var unknown_type
	 */
	private static $_instance = null;
	/**
	 * Connection to MySQL Database.
	 * @var unknown_type
	 */
	private $connection = null;
	/**
	 * configuration properties from config.ini file.
	 * @var array
	 */
	private $congif = array();


	private $parameters=array();

	/**
	 * Default Constructor for DataMySQL.
	 * @return DataMySQL
	 */
	public function __construct(){
		$this->config = __grp_param("database");
		$this->connection = mysql_connect(
						$this->config['servername'].":".$this->config['portnumber'],
						$this->config['username'],
						$this->config['password']);
		mysql_select_db($this->config['databasename']);
		__debug(__CLASS__."::__construct()=> database \"".$this->config['databasename']."\" connected.",__FILE__,__LINE__);

		//load system parameters
		if(empty($this->parameters)){
			$this->loadParameters();
		}

	}

	/**
	 * Default destructor for DataMySQL.
	 * @return unknown_type
	 */
	public function __destruct(){
		if($this->connection){
			mysql_close($this->connection);
		}
		__debug(__CLASS__."::__destruct() Database disconnected.");
	}
	
	/**
	 * Convert the description type (from model.ini) into the corresponding database field type.
	 * @param $type in the model.ini file.
	 * @return String the corresponding type into the MySql database.
	 */
	public function convertFieldType($fieldtype="",$size=0,$options="",$FK=false){
		$dbtype="";
		
		if($fieldtype!=""){
			switch($fieldtype){
				case "integer":
					$dbtype="BIGINT";
					break;
				case "string":
					$dbtype="VARCHAR($size)";
					break;
				case "text":
					if($size<=255){
						$dbtype="VARCHAR($size)";
					}elseif($size>255 && $size<=1000){
						$dbtype="TINYTEXT";
					}elseif($size>1000 && $size<=4000){
						$dbtype="MEDIUMTEXT";
					}elseif($size>4000 && $size<=20000){
						$dbtype="BIGTEXT";
					}else{
						$dbtype="TEXT($size)";
					}
					break;
				case "date":
					$dbtype="DATE";
					break;
				case "float":
					$dbtype="FLOAT";
					break;
			}
		}
		if($options!="" && !$FK){
			foreach($options as $key=>$option){
				switch($option){
					case "notnull":
						$dbtype.=" NOT NULL";
						break;
					case "auto":
						$dbtype.=" AUTO_INCREMENT";
				}
			}
		}
		__debug("convert $fieldtype into $dbtype");
		return $dbtype;
	}
	/**
	 * return the primary key of the $type.
	 * @param $type
	 * @return array of keys
	 */
	protected function getPrimaryKeyOf($type) {
	  $keys = Array();

	  $query = sprintf("SHOW KEYS FROM `%s`", $type);
	  $result = mysql_query($query) or die(mysql_error());

	  while ($row = mysql_fetch_assoc($result)) {
	    if ( $row['Key_name'] == 'PRIMARY' )
	      $keys[$row['Seq_in_index'] - 1] = $row['Column_name'];
	  }
	  __debug(__CLASS__."::getPrimaryKeyOf() retrieve primary key from $type: ".implode(",",$keys),__FILE__,__LINE__);
	  return $keys;
	}
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#loadParameters()
	 */
	protected function loadParameters(){
			__debug(__CLASS__."::loadParameters() load parameters from data table parameter");
			$list = $this->getEntities("parameter");
			//echo "<pre>".print_r($list,true)."</pre>";
			foreach($list as $item){
				$key=$item->key;
				$group=$item->group;
				$this->parameters[$group][$key] = $item->value;
				// replate $DATE$ placeholder by system date formated
				// following configuration date format.
				$this->parameters[$group][$key] = str_replace("%DATE%",date( __param("system","dateformat") ),$this->parameters[$group][$key]);
				__debug(__CLASS__."::loadParameteres() load parameter [$group|$key]".$this->parameters[$group][$key]);
			}
	}
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#getEntity()
	 */
	public function getEntity($type="",$id=""){
		global $framework;
		$keys=$this->getPrimaryKeyOf($type);
		if(count($keys)>1){
			$keys=implode("_",$keys);
		}else{
			$keys=$keys[0];
		}
		$rs = mysql_query("select * from $type where $keys='$id'".$this->getPagination(),$this->connection);
		$param = mysql_fetch_array($rs,MYSQL_ASSOC);
		__debug(__CLASS__."::getEntity() retrieve entity[$type:$id]",__FILE__,__LINE__);
		
		$this->requireEntity($type);
		$EntityClass= ucfirst($type);
		$entity = new $EntityClass($param);
		return $entity;
	}
	
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#getEntity()
	 */
	public function getEntityAssociates($type="",$id="", $associates=""){
		$param = array();
		if($type!=""){		
			$keys=$this->getPrimaryKeyOf($type);
			if(count($keys)>1){
				$keys=implode("_",$keys);
			}else{
				$keys=$keys[0];
			}
			//prepare name of the entity type to be manipulated.
			require_once("webapp/entities/$type.entity.php");
			$entityClass= ucfirst($type);
			
			$rs = mysql_query("select * from $type where $keys='$id'".$this->getPagination(),$this->connection);
			$param = mysql_fetch_array($rs,MYSQL_ASSOC);
			$entity = new $entityClass($param);
			if($associates!=""){
				foreach($associates as $associate){
					require_once("webapp/entities/$associate.entity.php");
					$assEntityClass= ucfirst($associate);
					
					$akeys = $this->getPrimaryKeyOf($associate);
					$awhere = "$associate.$type"."_id='$id'"; 
					$sql = "select * from $associate where $awhere".$this->getPagination();
					$ars = mysql_query($sql,$this->connection);
					$list2=array();
					$count=0;
					$associatesAttribute = $associate."s";
					//echo "<ul><li>$associate(".print_r($akeys,true)."</li><li>where='$awhere'</li><li>sql='$sql'</li><li>".count($ars)."</li></ul>";
					if($ars && count($ars)){
						while($item = mysql_fetch_array($ars,MYSQL_ASSOC)){
							$assEntity= new $assEntityClass($item);
							$list2 = $list2 + array($item[$akeys[0]]=>$assEntity);
							$count++;
						}
						
						$entity->$associatesAttribute = $list2;
					}else{
						$entity->$associatesAttribute = array();
					}
				}
			}
		}
		__debug(__CLASS__."::getEntity() retrieve entity[$type:$id] with ".implode(",",$associates)." entities",__FILE__,__LINE__);
		//return $param;
		return $entity;
	}
	
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#getEntities()
	 */
	public function getEntities($type=""){
		return $this->querySortedEntities($type,"","","");
	}
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#queryEntities()
	 */
	public function queryEntities($type="",$where=array()){
		return $this->querySortedEntities($type,$where,"","");
	}
	/**
	 * (non-PHPdoc)
	 * @see IData#querySortedEntities()
	 */
	public function querySortedEntities($type="",$where="",$sortorder="",$limit=""){

		$list2 = array();
		
		$swhere	= "";
		$ssortorder	= "";
		$slimit		= "";
		$count=0;

		if($type!=""){

			$this->requireEntity($type);
			$EntityClass= ucfirst($type);

			
			$keys=$this->getPrimaryKeyOf($type);
			if(count($keys)>1){
				$keys=implode("_",$keys);
			}else{
				$keys=$keys[0];
			}
			__debug(__CLASS__."::querySortedEntities($type,".
						print_r($where,true).",".
						print_r($sortorder,true).",".
						print_r($limit,true).",".
						")");
			if(!empty($keys)){
				$swhere=$this->whereString($where);
				/*
				if($where!=""){
					$swhere	= " where ";
					foreach($where as $whereclause){
						$swhere.=$whereclause['field'].$whereclause['comparator']."'".$whereclause['value']."'";
					}
				}*/
				$ssortorder = $this->sortString($sortorder);
				/*
				if($sortorder!=""){
					$ssortorder	= " order by ";
					foreach($sortorder as $sort){
						$ssortorder.=$sort['field']." ".$sort['direction'];
					}
				}
				*/
				if($limit!=""){
					$slimit = "limit ";
					$slimit.= (!empty($limit['offset'])?$limit['offset']:"0").",".(!empty($limit['max'])?$limit['max']:"0");
				}
				$sql="select * from $type $swhere $ssortorder ".$this->getPagination($slimit);
				__debug(__CLASS__."::querySortedEntities execute sql=\"".$sql."\"");
				$rs=mysql_query($sql,$this->connection);
				if($rs && count($rs)>0){
					while($item = mysql_fetch_array($rs,MYSQL_ASSOC)){

						$entity = new $EntityClass($item);
						//print_r($item);
						//print_r($entity);
										
						$list2 = $list2+array($item[$keys]=>$entity);
						$count++;
					}
					mysql_free_result($rs);
				}else{
					$list2=array();
				}

			}
		}
		__debug(__CLASS__."::querySortedEntities number of result=".count($list2));
		return $list2;
	}
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#setEntity()
	 */
	public function createEntity($type="",$entity=array()){

	}
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#setEntity()
	 */
	public function updateEntity($type="",$entity=array()){

	}
		/**
	 * (non-PHPdoc)
	 * @see modules/IData#setEntity()
	 */
	public function deleteEntity($type="",$entity=array()){
	}
	
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#getParam()
	 */
	public function getParam($group="",$key=""){
		$parameter=$this->getEntity("parameter",$group);
		return $parameter->value;
	}
	/**
	 * (non-PHPdoc)
	 * @see modules/IData#getGroupParam()
	 */
	public function getGroupParam($group=""){
		return $this->getEntity("parameter",$group);
	}
	/**
	 * create data $entity into the $type entity.
	 * @param $type
	 * @param $entity
	 * @return unknown_type
	 */
	protected function create($type="", $entity=array()){
	}

	/**
	 * update data $entity into the $type entity.
	 * @param $type
	 * @param $entity
	 * @return unknown_type
	 */
	protected function update($type="", $entity=array()){
	}
	/**
	 * delete data $entity into the $type entity.
	 * @param $type
	 * @param $entity
	 * @return unknown_type
	 */
	protected function delete($type="", $entity=array()){
	}

	/**
	 * Generate the pagination system.
	 * @param String $limit
	 * @return String containing the LIMIT statement for MySQL
	 */
	protected function getPagination($limit=""){
		if($limit==""){
			return " LIMIT ".$this->pageoffset.",".$this->pagesize;
		}else{
			return $limit;
		}
	}
	
	/**
	 * retrieve instance of the DataMySQL persistence manager.
	 * @return unknown_type
	 */
	public function getInstance(){
		if(empty(self::$_instance)){
			self::$_instance = new DataMySQL();
		}
		return self::$_instance;
	}
}
?>